package DBLayer;

import java.sql.*;
import java.util.ArrayList;

import DomainLayer.BradenScale;
import DomainLayer.TemperatureSchemeMeasurement;




public class DBBradenScale implements IFDBBradenScale {


	
		
		private Connection con;
		
		public DBBradenScale(){
			con = DbConnection1.getInstance().getDBcon();
			}
		
		public int createScheme(BradenScale scheme) throws Exception {	
			int rc = -1;
			String query = "INSERT INTO braden_scale (bscaleName, sensory_perception, moisture, activity, mobility, nutrition, friction_shear) VALUES('" + 
					scheme.getName() + "', '" +
					scheme.getSensoryPerception() + "', '" +
					scheme.getMoisture() + "', '" +
					scheme.getActivity() + "', " +
					scheme.getMobility() +  "," +
					scheme.getNutrition() + ", '" +
					scheme.getFrictionAndShear() + "')" ;
			
			System.out.println("insert : " + query);
			
			try{Statement stmt = con.createStatement();
					stmt.setQueryTimeout(5);
					rc = stmt.executeUpdate(query);
					stmt.close();
					}catch(SQLException ex){
						System.out.println("braden_scale does not exist");
						throw new Exception ("insertion was not correct");
					}
			return rc;
			
			
	}

	@Override
	public int deleteScheme(String name) {
	        
		int rc = -1;
		String query="DELETE FROM Braden_Scale WHERE bscaleName = '" +
				name + "'";
	         System.out.println(query);
		try{ // delete from employee
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
		  	rc = stmt.executeUpdate(query);
		  	stmt.close();
		}//slut try	
	        catch(Exception ex){
		  	System.out.println("Delete exception in  db: "+ex);
	        }
		return rc;
		
		
	}

	@Override
	public BradenScale getScheme(String name) {
		String wClause = " bscaleName = '" + name + "'";
		return singleWhere(wClause);
	}

	@Override
	public int updateScheme(BradenScale scheme) {
		BradenScale BradenScaleObj  = scheme;
		int rc=-1;
	  
		String query="UPDATE Braden_Scale SET "+
		 	  "sensory_perception ='"+ BradenScaleObj.getSensoryPerception() + "', " +
		 	  "moisture ='"+ BradenScaleObj.getMoisture() + "', "+
		 	  "activity ='" + BradenScaleObj.getActivity() + "', "+
		 	  "mobility=" + BradenScaleObj.getMobility() + ", "+
		 	  "nutrition ='" + BradenScaleObj.getNutrition() + "', "+
		 	  "friction_shear ='" + BradenScaleObj.getFrictionAndShear() + "' "+
		          " WHERE bscaleName = '"+ BradenScaleObj.getName() + "'";
                System.out.println("Update query:" + query);
  		try{ // update employee
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);
                      
	 	 	stmt.close();
		}//end try	
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in employee db: "+ex);
	  	}
		return rc;
		
	}
	
	
	 private BradenScale singleWhere(String wClause)
	  {
	   ResultSet results;
	   BradenScale BradenScaleObj = new BradenScale();

	   	String query =  buildQuery(wClause);
	                 System.out.println(query);
	   try{ // read the employee from the database
	     Statement stmt = con.createStatement();
	     stmt.setQueryTimeout(5);
	     results = stmt.executeQuery(query);
	     
	     if( results.next() ){
	                             BradenScaleObj = buildBradenScale(results);
	                             //association is to be build
	                            
	                             stmt.close();
	                            
	     			}
	                         else{ //no braden_scale was found
	                             BradenScaleObj = null;
	                         }
	   }//end try 
	    catch(Exception e){
	     System.out.println("Query exception: "+e);
	    }
	   return BradenScaleObj;
	  }
	 
	 //method to build a query
	 private String buildQuery(String wClause)
	 {
	     String query="SELECT bscaleName, sensory_perception, moisture, activity, mobility, nutrition, friction_shear FROM Braden_Scale";
	  
	  if (wClause.length()>0)
	   query=query+" WHERE "+ wClause;
	   
	  return query;
	 }
	 
	 private BradenScale buildBradenScale(ResultSet results)
     {   BradenScale BradenScaleObj = new BradenScale();
         try{ // the columns from the table emplayee  are used
        	   BradenScaleObj.setName(results.getString("bscaleName"));
        	   BradenScaleObj.setSensoryPerception(results.getInt("sensory_perception"));
        	   BradenScaleObj.setMoisture(results.getInt("moisture"));
        	   BradenScaleObj.setActivity(results.getInt("activity"));
        	   BradenScaleObj.setMobility(results.getInt("mobility"));
        	   BradenScaleObj.setNutrition(results.getInt("nutrition"));
               BradenScaleObj.setFrictionAndShear(results.getInt("friction_shear"));
               
                 }
        catch(Exception e)
        {
            System.out.println("error in building the braden_scale object");
        }
        return BradenScaleObj;
     }
	 
		private ArrayList<BradenScale> miscWhere(String wClause) {
			ResultSet results;
			ArrayList<BradenScale> list = new ArrayList<BradenScale>();

			String query = buildQuery(wClause);

			try { // read the employee from the database
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				results = stmt.executeQuery(query);

				while (results.next()) {
					BradenScale scheme = new BradenScale();
					scheme = buildBradenScale(results);
					list.add(scheme);
				}// end while
				stmt.close();

			}// slut try
			catch (Exception e) {
				System.out.println("Query exception - select: " + e);
				e.printStackTrace();
			}
			return list;

		}
		
		public ArrayList<BradenScale> getAllBradenScales() {
			return miscWhere("");
		}
		
		

}
